<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class penduduk_model extends CI_model {

    function __construct() {
        parent::__construct();
        $this->load->database();
    }

    public function get_biodata_by_nik($nik) {
        if (!$nik)
            $nik = 0;
        return $this->db->query("SELECT * FROM biodata_wni_bgt WHERE NIK = $nik");
    }

    public function getChart($id_kec = 0, $id_kel = 0) {
        $filter = array();
        $filter['kode_kecamatan'] = 0;
        $filter['kode_kelurahan'] = 0;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $filter['kode_kecamatan'] = $id_kec;
        }
        if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
            $filter['kode_kelurahan'] = $id_kel;
        }
        $query = '';
        if ($filter['kode_kecamatan'] == 0 && $filter['kode_kelurahan'] == 0) {
            $this->db->select('sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, kode_kecamatan as kategori_id, nama_kecamatan as kategori');
            $this->db->group_by('kode_kecamatan')
                    ->order_by('kode_kecamatan');
            $this->db->where('nama_kelurahan is not null');
            $query = $this->db->get('penduduk_per_kelurahan');
            return $query->result();
        } else if ($filter['kode_kecamatan'] != 0 && $filter['kode_kelurahan'] != 0) {
            $this->db->select('sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, kode_kelurahan as kategori_id, nama_kelurahan as kategori');
            $this->db->group_by('kode_kelurahan')
                    ->order_by('kode_kelurahan');
            $this->db->where('nama_kelurahan is not null');
            $this->db->where($filter);
            $query = $this->db->get('penduduk_per_kelurahan');
            return $query->result();
        } else if ($filter['kode_kecamatan'] != 0) {
            $this->db->select('sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, kode_kelurahan as kategori_id, nama_kelurahan as kategori');
            $this->db->group_by('kode_kelurahan')
                    ->order_by('kode_kelurahan');
            $this->db->where('nama_kelurahan is not null');
            $this->db->where(array('kode_kecamatan' => $filter['kode_kecamatan']));
            $query = $this->db->get('penduduk_per_kelurahan');
            return $query->result();
        }
        //echo $this->db->last_query();
    }

    public function get_list_kecamatan() {
        $this->db->select('no_kec, nama_kec');
        $this->db->order_by('no_kec asc');
        return $this->db->get('setup_kec')->result();
    }

    public function get_list_kelurahan($id_kec) {
        $this->db->select('no_kel, nama_kel');
        $this->db->where(array('no_kec' => $id_kec));
        return $this->db->get('setup_kel')->result();
    }

    public function get_jenis_kelamin($id) {
        $sql = "SELECT KET FROM m_kawin WHERE KODE = $id";
        return $this->db->query($sql)->result_array()[0]["KET"];
    }

    public function get_agama($id) {
        $sql = "SELECT * FROM agama_master WHERE NO = $id";
        return $this->db->query($sql)->result_array()[0]["DESCRIP"];
    }

    public function get_jenis_pekerjaan($id) {
        $sql = "SELECT DESCRIP FROM pkrjn_master WHERE NO = $id";
        return $this->db->query($sql)->result_array()[0]["DESCRIP"];
    }

    public function get_pendidikan_akhir($id) {
        $sql = "SELECT DESCRIP FROM pddkn_master WHERE NO = $id";
        return $this->db->query($sql)->result_array()[0]["DESCRIP"];
    }

    public function get_gol_darah($id) {
        $sql = "SELECT KET FROM m_darah WHERE KODE = $id";
        return $this->db->query($sql)->result_array()[0]["KET"];
    }

    public function get_status_kawin($id) {
        $sql = "SELECT KET FROM m_kawin WHERE KODE = $id";
        return $this->db->query($sql)->result_array()[0]["KET"];
    }

    public function get_kec_kode($kec_id) {
        if (is_numeric($kec_id) && $kec_id > 0) {
            $this->db->where(array('kec_id' => $kec_id));
            $this->db->select('kec_kode');
            $q = $this->db->get('kecamatan');
            return $q->result()[0];
        }
        return array();
    }

    public function get_kec_nama($kec_id) {
        if (is_numeric($kec_id) && $kec_id > 0) {
            $this->db->where(array('no_kec' => $kec_id))
                    ->select('nama_kec');
            $q = $this->db->get('setup_kec');
            foreach ($q->result() as $row) {
                return $row->nama_kec;
            }
        }return "";
    }

    public function get_kel_nama($kec_id = 0, $kel_id = 0) {
        if (is_numeric($kec_id) && $kec_id > 0 && is_numeric($kel_id) && $kel_id > 0) {
            $this->db->where(array('no_kec' => $kec_id, 'no_kel' => $kel_id))
                    ->select('nama_kel');
            $q = $this->db->get('setup_kel');
            foreach ($q->result() as $row) {
                return $row->nama_kel;
            }
        }return "";
    }

    public function get_jumlah_data() {
        $sql = "select count(*) as jumlah_data from biodata_wni_bgt";
        $query = $this->db->query($sql);
        $rows = $query->result();
        $row = $rows[0];
        return $row->jumlah_data;
    }

    public function get_data_penduduk_by_jenis_kelamin($kunci, $limit, $halaman, $id_kec, $id_kel) {
        $sql = "select * from m_jnskel where kode=$kunci";
        $query = $this->db->query($sql);
        $filter = array();
        foreach ($query->result() as $key => $row) {
            $filter[] = "jenis_klmin=$kunci";
            break;
        }

        $sql = "select * from setup_kec where no_kec=$id_kec";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kec=$id_kec";
            break;
        }

        $sql = "select * from setup_kel where no_kel=$id_kel";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kel=$id_kel";
            break;
        }
        $s_filter = implode(' and ', $filter);
        if (strlen($s_filter)) {
            $s_filter = ' where ' . $s_filter;
        }
        $sql = "select *, jenis_klmin as kategori_id "
                . "from biodata_wni_bgt  $s_filter  "
                . "limit $halaman, $limit ";

        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_jumlah_data_by_jenis_kelamin($kunci, $id_kec, $id_kel) {
        $sql = "select * from m_jnskel where kode=$kunci";
        $query = $this->db->query($sql);
        $filter = array();
        foreach ($query->result() as $key => $row) {
            $filter[] = "jenis_klmin=$kunci";
            break;
        }

        $sql = "select * from setup_kec where no_kec=$id_kec";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kec=$id_kec";
            break;
        }

        $sql = "select * from setup_kel where no_kel=$id_kel";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kel=$id_kel";
            break;
        }
        $s_filter = implode(' and ', $filter);
        if (strlen($s_filter) > 0) {
            $s_filter = ' where ' . $s_filter;
        }
        $sql = "select count(*) as jumlah from biodata_wni_bgt  "
                . " $s_filter ";
        //echo $sql;
        $query = $this->db->query($sql);
        return $query->result()[0]->jumlah;
    }

    public function get_list_jenis_kelamin() {
        $sql = "select KODE as kategori_id, KET as kategori from m_jnskel";
        $query = $this->db->query($sql);
        return $query->result();
    }

    function get_jumlah_data_miskin($kunci, $id_kec, $id_kel) {
        $jenis_kelamin = '';
        if (strlen($id_kec) > 0 && is_numeric($id_kec) && $id_kec > 0) {
            $id_kec = (int) $id_kec;
            if (strlen($id_kel) > 0 && is_numeric($id_kel) && $id_kel > 0) {
                $id_kel = (int) $id_kel;
                if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                    $jenis_kelamin = " and jenis_kelamin=$kunci ";
                }
                $sql = "select count(*) as jumlah from miskin_warga where "
                        . "kode_kecamatan=$id_kec $jenis_kelamin and "
                        . "fid_keluarga "
                        . "in ("
                        . "select id_rt "
                        . "from miskin_rumahtangga "
                        . "where kecamatan=$id_kec and kelurahan=$id_kel "
                        . ") ";
                $query = $this->db->query($sql);
                return $query->result()[0]->jumlah;
            } else {
                if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                    $jenis_kelamin = " and jenis_kelamin=$kunci ";
                }
                $sql = "select count(*) as jumlah from miskin_warga  where "
                        . "kode_kecamatan=$id_kec $jenis_kelamin";
                //echo $sql;
                $query = $this->db->query($sql);
                return $query->result()[0]->jumlah;
            }
        } else {
            if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                $jenis_kelamin = " where jenis_kelamin=$kunci ";
            }
            $sql = "select count(*) as jumlah from miskin_warga  $jenis_kelamin ";
            //echo $sql;
            $query = $this->db->query($sql);
            return $query->result()[0]->jumlah;
        }
    }

    function getJumlahPenduduk($id_kec = 0, $id_kel = 0) {
        if (strlen($id_kec) > 0 && is_numeric($id_kec) && $id_kec > 0) {
            if (strlen($id_kel) > 0 && is_numeric($id_kel) && $id_kel > 0 && false) {
                $sql = "select sum(jumlah_penduduk) as jumlah from penduduk_per_kelurahan "
                        . "where kode_kecamatan=$id_kec and id_kelurahan=$id_kel";
                $query = $this->db->query($sql);
                $hasil = $query->result();
                $jumlah = $hasil[0]->jumlah;
                return $jumlah;
            } else {
                $sql = "select sum(jumlah_penduduk) as jumlah from penduduk_per_kelurahan "
                        . "where kode_kecamatan=$id_kec";
                $query = $this->db->query($sql);
                $hasil = $query->result();
                $jumlah = $hasil[0]->jumlah;
                return $jumlah;
            }
        } else {
            $sql = "select sum(jumlah_penduduk) as jumlah from penduduk_per_kelurahan";
            $query = $this->db->query($sql);
            $hasil = $query->result();
            $jumlah = $hasil[0]->jumlah;
            return $jumlah;
        }
    }

    function get_data_penduduk_miskin($kunci, $limit, $halaman, $id_kec, $id_kel) {
        if (strlen($id_kec) > 0 && is_numeric($id_kec) && $id_kec > 0) {
            $id_kec = (int) $id_kec;
            if (strlen($id_kel) > 0 && is_numeric($id_kel) && $id_kel > 0) {
                $id_kel = (int) $id_kel;
                $jenis_kelamin = '';
                if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                    $jenis_kelamin = " and jenis_kelamin=$kunci ";
                }
//                $sql = "select *, jenis_kelamin as kategori_id "
//                        . "from miskin_warga m "
//                        . "where "
//                        . "kode_kecamatan=$id_kec $jenis_kelamin and "
//                        . "fid_keluarga "
//                        . "in ("
//                        . "select id_rt "
//                        . "from miskin_rumahtangga "
//                        . "where kecamatan=$id_kec and kelurahan=$id_kel "
//                        . ") "
//                        . "limit $halaman, $limit";
                $sql="select m.*,t.*, m.jenis_kelamin as kategori_id "
                        . "from miskin_warga m "
                        . "inner join miskin_rumahtangga t "
                        . "on m.fid_keluarga=t.id_rt "
                        . "and t.kode_kecamatan=m.kode_kecamatan "
                        . "where t.kode_kecamatan=$id_kec $jenis_kelamin "
                        . "and t.kelurahan=$id_kel "
                        . "limit $halaman, $limit";
                //echo $sql;
                $query = $this->db->query($sql);
                return $query->result();
            } else {
                $jenis_kelamin = '';
                if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                    $jenis_kelamin = " and jenis_kelamin=$kunci ";
                }
                $sql = "select *, jenis_kelamin as kategori_id from miskin_warga  where "
                        . "kode_kecamatan=$id_kec $jenis_kelamin "
                        . "limit $halaman, $limit";
                $sql="select m.*,t.*, m.jenis_kelamin as kategori_id "
                        . "from miskin_warga m "
                        . "inner join miskin_rumahtangga t "
                        . "on m.fid_keluarga=t.id_rt "
                        . "and t.kode_kecamatan=m.kode_kecamatan "
                        . "where t.kode_kecamatan=$id_kec $jenis_kelamin "
                        
                        . "limit $halaman, $limit";
                //echo $sql;
                $query = $this->db->query($sql);
                return $query->result();
            }
        } else {
            $jenis_kelamin = '';
            if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                $jenis_kelamin = " where jenis_kelamin=$kunci ";
            }
            $sql = "select *, jenis_kelamin as kategori_id from miskin_warga  $jenis_kelamin "
                    . "limit $halaman, $limit";
            $sql="select m.*,t.*, m.jenis_kelamin as kategori_id "
                        . "from miskin_warga m "
                        . "inner join miskin_rumahtangga t "
                        . "on m.fid_keluarga=t.id_rt "
                        . "and t.kode_kecamatan=m.kode_kecamatan "
                        . " $jenis_kelamin "
                        . "limit $halaman, $limit";
            //echo $sql;
            $query = $this->db->query($sql);
            return $query->result();
        }
    }

}
